﻿/**************************************
*文书类
* 
* ************************************/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DreamBuilder.Core.Data;
using CommonModule;
using ePlanet.ArchiveMange.Models;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Oracle;
using System.Data.Common;

namespace ePlanet.ArchiveMange.DataAccess
{
    public class OilSearchArchiveDAL : DataHelper
    {
        public OilSearchArchiveDAL()
        {
            if (!(this.DBHelper.database is OracleDatabase))
            {
                throw new Exception("只支持Oracle数据库");
            }
        }

        public ArchiveMangeModal.OLISEARCH_ARCHIVEDataTable GetAllDocumenArchivetData()
        {
            string sql = @"select t.dataid,t.archivenum,t.tempordernum,t.archivesource,t.fullfondsnum,t.title,t.archivetype,t.publishorgid,t.publisher,t.publishtime,t.fileorgid,t.f_keeptime,t.f_carriertype
,t.f_securitytype,t.pagenum,t.mainbodynum,t.wellnumber,t.welladdress,t.welltype,t.summary,t.remark,t.storagetype,t.filenum,t.boxnum,t.expansioninfo,t.hujianhao,
t.createuserid,t.createtime,t.lastupdateuser,t.lastupdatetime,t.statusid,t.f_keepsecuritytime
from olisearch_archive t";
            ArchiveMangeModal.OLISEARCH_ARCHIVEDataTable data = new ArchiveMangeModal.OLISEARCH_ARCHIVEDataTable();
            if (this.DBHelper.database is OracleDatabase)
            {
                DataSet ds = this.DBHelper.ExecuteDataSet(sql);
                if (ds != null && ds.Tables.Count > 0)
                {
                    ds.Tables[0].TableName = data.TableName;
                    data.Merge(ds.Tables[0]);
                }
                return data;
            }
            else
            {
                throw new Exception("只支持Oracle数据库");
            }
        }


        /// <summary>
        /// 根据主键查询数据
        /// </summary>
        /// <param name="typeId"></param>
        /// <returns></returns>
        public ArchiveMangeModal.OLISEARCH_ARCHIVEDataTable GetAllDocumenArchivetDataById(string DATAID)
        {
            string sql = @"select t.dataid,t.archivenum,t.tempordernum,t.archivesource,t.fullfondsnum,t.title,t.archivetype,t.publishorgid,t.publisher,t.publishtime,t.fileorgid,t.f_keeptime,t.f_carriertype
,t.f_securitytype,t.pagenum,t.mainbodynum,t.doplicatebodynum,t.wellnumber,t.welladdress,t.welltype,t.summary,t.remark,t.storagetype,t.filenum,t.boxnum,t.expansioninfo,t.hujianhao,
t.createuserid,t.createtime,t.lastupdateuser,t.lastupdatetime,t.statusid,t.f_keepsecuritytime,t.f_catagorytype 
from olisearch_archive t where DATAID='" + DATAID + "'";
            ArchiveMangeModal.OLISEARCH_ARCHIVEDataTable data = new ArchiveMangeModal.OLISEARCH_ARCHIVEDataTable();
            if (this.DBHelper.database is OracleDatabase)
            {
                DataSet ds = this.DBHelper.ExecuteDataSet(sql);
                if (ds != null && ds.Tables.Count > 0)
                {
                    data.Merge(ds.Tables[0]);
                }
                return data;
            }
            else
            {
                throw new Exception("只支持Oracle数据库");
            }
        }

        /// <summary>
        /// 备注：根据DATAID列表取对应档案的详细信息
        /// 时间：2013年1月9日 23:47:41
        /// </summary>
        /// <param name="idList"></param>
        /// <returns></returns>
        public DataTable GetOilSearchArchiveDataByIds(IEnumerable<string> idList)
        {
            if (idList == null || idList.Count() == 0)
            {
                return null;
            }
            var ids = string.Join(",", (from id in idList select string.Format("'{0}'", id)).ToArray());
            string sql = string.Format(@"select rownum rn, 
       a.createuserid,
       a.dataid, 
       a.statusid,
       a.fullfondsnum,
       a.archivenum,
       a.archivesource,
       a.title,
       nvl(a.pagenum, 0) pagenum,--页数
       a.filenum,  --文件编号
       0 pagefilenum, --件数
       to_char(a.publishtime ,'yyyy-mm-dd') publishtime,
       a.createtime,
       a.publisher,
       a.fileorgid,
       a.mainbodynum,--正本数量
       nvl(a.doplicatebodynum,0) doplicatebodynum, --副本数量
       1 fs,
       '件' remark,
       b.code_desc as f_keeptime,
       c.code_desc as f_securitytype
  from olisearch_archive a
  left join p_bt_codetable b
    on a.f_keeptime = b.codetableid
  left join p_bt_codetable c
  on a.f_securitytype = c.codetableid
  where a.DATAID  in ({0})", ids);
            if (this.DBHelper.database is OracleDatabase)
            {
                DataSet ds = this.DBHelper.ExecuteDataSet(sql);
                if (ds != null && ds.Tables.Count > 0)
                {
                    return ds.Tables[0];
                }
                return null;
            }
            else
            {
                throw new Exception("只支持Oracle数据库");
            }
        }

        public DataTable GetHandoverDataByIds(IEnumerable<string> idList)
        {
            if (idList == null || idList.Count() == 0)
            {
                return null;
            }
            var ids = string.Join(",", (from id in idList select string.Format("'{0}'", id)).ToArray());
            string sql = string.Format(@"SELECT A.DATAID,
       A.ARCHIVESOURCE,
       A.CREATETIME,
       A.FILEORGID,
       A.MAINBODYNUM, --正本数量
       A.doplicatebodynum,--副本数量
       B.CODE_DESC     AS F_CARRIERTYPE, --载体类型
       C.CODE_DESC     AS F_KEEPTIME       --保管期限
  FROM olisearch_archive A
  LEFT JOIN P_BT_CODETABLE B
    ON A.F_CARRIERTYPE = B.CODETABLEID
  LEFT JOIN P_BT_CODETABLE C
    ON A.F_KEEPTIME = C.CODETABLEID
  where a.DATAID  in ({0})", ids);
            if (this.DBHelper.database is OracleDatabase)
            {
                DataSet ds = this.DBHelper.ExecuteDataSet(sql);
                if (ds != null && ds.Tables.Count > 0)
                {
                    return ds.Tables[0];
                }
                return null;
            }
            else
            {
                throw new Exception("只支持Oracle数据库");
            }
        }

        public DataTable GetArchiveDateSpanByIds(IEnumerable<string> idList)
        {
            if (idList == null || idList.Count() == 0)
            {
                return null;
            }
            var ids = string.Join(",", (from id in idList select string.Format("'{0}'", id)).ToArray());
            string sql = string.Format(@"SELECT   MAX(a.publishtime) endtime , MIN(a.publishtime) starttime
FROM olisearch_archive a 
WHERE a.dataid IN ({0})", ids);
            if (this.DBHelper.database is OracleDatabase)
            {
                DataSet ds = this.DBHelper.ExecuteDataSet(sql);
                if (ds != null && ds.Tables.Count > 0)
                {
                    return ds.Tables[0];
                }
                return null;
            }
            else
            {
                throw new Exception("只支持Oracle数据库");
            }
        }

        /// <summary>
        /// 根据档案类型查询数据
        /// </summary>
        /// <param name="typeId"></param>
        /// <returns></returns>
        public ArchiveMangeModal.OLISEARCH_ARCHIVEDataTable GetArchiveByType(string archiveType)
        {
            string sql = @"select t.dataid,t.archivenum,t.tempordernum,t.archivesource,t.fullfondsnum,t.title,t.archivetype,t.publishorgid,t.publisher,t.publishtime,t.fileorgid,t.f_keeptime,t.f_carriertype
,t.f_securitytype,t.pagenum,t.mainbodynum,t.wellnumber,t.welladdress,t.welltype,t.summary,t.remark,t.storagetype,t.filenum,t.boxnum,t.expansioninfo,t.hujianhao,
t.createuserid,t.createtime,t.lastupdateuser,t.lastupdatetime,t.statusid,t.f_keepsecuritytime
from olisearch_archive t where archiveType='" + archiveType + "'";
            ArchiveMangeModal.OLISEARCH_ARCHIVEDataTable data = new ArchiveMangeModal.OLISEARCH_ARCHIVEDataTable();
            if (this.DBHelper.database is OracleDatabase)
            {
                DataSet ds = this.DBHelper.ExecuteDataSet(sql);
                if (ds != null && ds.Tables.Count > 0)
                {
                    data.Merge(ds.Tables[0]);
                }
                return data;
            }
            else
            {
                throw new Exception("只支持Oracle数据库");
            }
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public bool DeleteData(string key)
        {
            string sql = "delete from olisearch_archive where DATAID=:DATAID";
            DbCommand cmd = this.DBHelper.database.GetSqlStringCommand(sql);
            this.DBHelper.database.AddParameter(cmd, "DATAID", DbType.String, ParameterDirection.Input, "DATAID", DataRowVersion.Current, key);
            int rowcount = this.DBHelper.database.ExecuteNonQuery(cmd);
            return rowcount > 0 ? true : false;
        }

        public bool IsSameSecret(List<Guid> dataIDList)
        {
            return true;
        }
    }
}
